set more off
log using ch5.log, replace

*****************
* Preparing data* 
*****************

* Copy-pasting demographic information in the Bavaria panel study (from the regional election to the national election) 
*use "C:\Users\Filip\Dropbox\MEDW data\Andr�'s papers\turnout Andr�\Aggregated_17march2016_new.dta", clear
use "C:\Users\sgolder\Dropbox\Projects\MEDW\ch5_turnout\New folder\ch5_replication_SG\Aggregated_17march2016_new.dta", clear
keep if ELECID==13
keep RESPID education YOB Q5 GEND Q42B Q43B 
rename (RESPID education YOB Q5 GEND Q42B Q43B) (key education_n YOB_n  Q5_n  GEND_n  Q42B_n  Q43B_n)
sort key 
*save "C:\Users\Filip/Dropbox/medw data/Surveys/Datasets/temp_bav.dta", replace
save "C:\Users\sgolder\Dropbox\Projects\MEDW\ch5_turnout\New folder\temp_bav.dta", replace

*use "C:\Users\Filip\Dropbox\MEDW data\Andr�'s papers\turnout Andr�\Aggregated_17march2016_new.dta", clear
use "C:\Users\sgolder\Dropbox\Projects\MEDW\ch5_turnout\New folder\ch5_replication_SG\Aggregated_17march2016_new.dta", clear
gen double key =. 
replace key = RESPID if ELECID ==12 
sort key 

*merge m:m key using "C:\Users\Filip/Dropbox/MEDW data/Surveys/Datasets/temp_bav.dta" // update
merge m:m key using "C:\Users\sgolder\Dropbox\Projects\MEDW\ch5_turnout\New folder\ch5_replication_SG\temp_bav.dta" // update
drop if _merge ==2
fre ELECID if _merge==3 // Checking the merger (other checks were performed elsewhere)
replace education = education_n if ELECID == 12
replace YOB = YOB_n if ELECID == 12
replace Q5 = Q5_n if ELECID == 12
replace GEND = GEND_n if ELECID == 12 
replace Q42B = Q42B_n if ELECID == 12 
replace Q43B = Q43B_n if ELECID == 12
drop key education_n YOB_n  Q5_n  GEND_n  Q42B_n  Q43B_n _merge 
*erase "C:\Users\Filip/Dropbox/MEDW data/Surveys/Datasets/temp_bav.dta"
erase "C:\Users\sgolder\Dropbox\Projects\MEDW\ch5_turnout\New folder\ch5_replication_SG\temp_bav.dta"

* Dropping Switzerland and Canada 
drop if inlist(COUNTRY, 1, 5) 

* Creating variables
********************
* Voting or not
gen Voted=.
replace Voted=1 if PQ5_2==1
replace Voted=0 if PQ5_2==2 | PQ5_2==9
replace Voted=1 if PQ5_1==4
replace Voted=0 if PQ5_1==1 | PQ5_1==2 | PQ5_1==3 | PQ5_1==9
drop if Voted==.

* Education (standardized)
rename education Education 
drop if Education==99
drop if Education==.
replace Education=Education/10 if COUNTRY==1
replace Education=Education/10 if COUNTRY==2
replace Education=Education/6 if COUNTRY==3
replace Education=Education/7 if COUNTRY==4
replace Education=Education/11 if COUNTRY==5

* Age
gen Age=ELYEAR-YOB

* Political interest
drop if Q5==99
drop if Q5==.
gen Interest=Q5
replace Interest=Interest/10

* Gender
gen Female=GEND
drop if Female==.
replace Female=0 if GEND==1
replace Female=1 if GEND==2

* Regional and European election dummies
gen National=0
replace National=1 if LEVEL==1
gen Regional=0
replace Regional=1 if LEVEL==2
gen Europe=0 
replace Europe=1 if LEVEL==3

* Policy impact
gen Impact=Q42B if LEVEL==1
replace Impact=Q42A if LEVEL==3
replace Impact=Q42D if LEVEL==4
replace Impact=Q42C if LEVEL==2
drop if Impact==99
drop if Impact==. & ELECID!=22 & ELECID!=23

* Attachment
gen Attachment=Q43B if LEVEL==1
replace Attachment=Q43A if LEVEL==3
replace Attachment=Q43D if LEVEL==4
replace Attachment=Q43C if LEVEL==2
drop if Attachment==99
drop if Attachment==. & ELECID!=22 & ELECID!=23

* Importance of election
gen Importance=Q34B if LEVEL==1
replace Importance=Q34A if LEVEL==2
replace Importance=Q34D if LEVEL==3
replace Importance=Q34C if LEVEL==4
drop if Importance==99
drop if Importance==.

*Preparing contact variables
foreach var of varlist PQ17_phone PQ17_pesonal PQ17_post PQ17_email PQ17_leaflet PQ17_other {  
replace `var'=0 if `var'==2 | `var'==9
} 
label define contact 1"contact: yes" 0"contact: no" 
label values PQ17_phone PQ17_pesonal PQ17_post PQ17_email PQ17_leaflet PQ17_other contact
fre PQ17_phone PQ17_pesonal PQ17_post PQ17_email PQ17_leaflet PQ17_other

* Contact 
gen filter_contact = PQ17_phone ==. & PQ17_pesonal ==. & PQ17_post ==. & PQ17_email==.  //
gen Contact = 0 
replace Contact = PQ17_phone + PQ17_pesonal + PQ17_post+ PQ17_email 
replace Contact =. if filter_contact ==1
fre Contact 
drop if Contact ==. 

* Creating weights
******************

* Weighting using the actual turnout (which is presented in Table 5.1.)

** Calculating Reported turnout 
gen rep_turnout =.  
forvalues i = 1/27 {
cap tab Voted if ELECID==`i', matcell(x) 
*matrix list x
cap replace rep_turnout = x[2,1]/(x[2,1]+x[1,1]) if ELECID==`i'
}
tabstat rep_turnout, by(ELECID)

* Real Turnout 
gen real_turnout = OFFICIAL_TURNOUT

*Turnout weight 
gen DVWEIGHT = . 
forvalues i = 1/27 {
capture noisily display "ELECID" `i' 
replace DVWEIGHT=real_turnout/rep_turnout if ELECID==`i' & Voted==1
replace DVWEIGHT=(1-real_turnout)/(1-rep_turnout) if ELECID==`i' & Voted==0 
}


*Filter: not necessary: at this stage, there is no missing value except for Municipal elections in France 
* where attachment & impact are missing. 

fre ELECID if Voted ==. | Importance ==. | Attachment==. |  Impact==. |  Contact==. | ///
 Female==. | Interest==. |  Age==. |  Education==. |   Europe==. |  Regional==. 
fre Voted Importance   Attachment   Impact   Contact  ///
 Female  Interest   Age   Education    Europe   Regional if inlist(ELECID, 22, 23)

*LEVEL2 // for a more convenient export of the results from the following tables
recode LEVEL (1=2 "National") (3=1 "Supranational") (2 4 = 3 "Subnational"), gen(LEVEL2) 
	



* Table 5.2 : Mean importance rating
*putexcel set "C:\Users\Filip\Dropbox\MEDW data\Andr�'s papers\turnout Andr�\Tables_turnout_multilevel.xlsx", sheet(T5.2_Importance) modify
putexcel set "C:\Users\sgolder\Dropbox\Projects\MEDW\ch5_turnout\New folder\ch5_replication_SG\Tables_turnout_multilevel.xlsx", sheet(T5.2_Importance) modify
putexcel B2=("Europe") B3=("National") B4=("Regional") B5=("Municipal") 
putexcel C1=("Ile-de-France") D1=("Provence") E1=("	Bavaria	") 
putexcel F1=("Lower Saxony") G1=("Catalonia") H1=("Madrid")

*IDF
tabstat Importance if REGIONID==201 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x //usewritten command 
putexcel C2=(x[1,1]) C3=(x[2,1]) C5=(x[3,1])

*PACA
tabstat Importance if REGIONID==202 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel D2=(x[1,1]) D3=(x[2,1]) D5=(x[3,1])  

*Bavaria
tabstat Importance if REGIONID==402 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel E2=(x[1,1]) E3=(x[2,1]) E4=(x[3,1]) // watchout E4!!! not E5 

*Lower Saxony 
tabstat Importance if REGIONID==401 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel F2=(x[1,1]) F3=(x[2,1]) F4=(x[3,1]) 

*Catalonia
tabstat Importance if REGIONID==301 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel G2=(x[1,1]) G3=(x[2,1]) G4=(x[3,1]) 

*Madrid
tabstat Importance if REGIONID==302 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel H2=(x[1,1]) H3=(x[2,1]) H4=(x[3,1]) 


* Table 5.3 : Mean impact rating

*putexcel set "C:\Users\Filip\Dropbox\MEDW data\Andr�'s papers\turnout Andr�\Tables_turnout_multilevel.xlsx", sheet(T5.3_Impact) modify
putexcel set "C:\Users\sgolder\Dropbox\Projects\MEDW\ch5_turnout\New folder\ch5_replication_SG\Tables_turnout_multilevel.xlsx", sheet(T5.3_Impact) modify
putexcel B2=("Europe") B3=("National") B4=("Regional") 
putexcel C1=("Ile-de-France") D1=("Provence") E1=("	Bavaria	") 
putexcel F1=("Lower Saxony") G1=("Catalonia") H1=("Madrid")

*IDF
tabstat Impact if REGIONID==201 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x //usewritten command 
putexcel C2=(x[1,1]) C3=(x[2,1]) C5=(x[3,1])

*PACA
tabstat Impact if REGIONID==202 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel D2=(x[1,1]) D3=(x[2,1]) D5=(x[3,1])  

*Bavaria
tabstat Impact if REGIONID==402 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel E2=(x[1,1]) E3=(x[2,1]) E4=(x[3,1]) // watchout E4!!! not E5 

*Lower Saxony 
tabstat Impact if REGIONID==401 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel F2=(x[1,1]) F3=(x[2,1]) F4=(x[3,1]) 

*Catalonia
tabstat Impact if REGIONID==301 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel G2=(x[1,1]) G3=(x[2,1]) G4=(x[3,1]) 

*Madrid
tabstat Impact if REGIONID==302 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel H2=(x[1,1]) H3=(x[2,1]) H4=(x[3,1]) 


* Table 5.4 : Mean attachment rating
*putexcel set "C:\Users\Filip\Dropbox\MEDW data\Andr�'s papers\turnout Andr�\Tables_turnout_multilevel.xlsx", sheet(T5.4_Attachment) modify
putexcel set "C:\Users\sgolder\Dropbox\Projects\MEDW\ch5_turnout\New folder\ch5_replication_SG\Tables_turnout_multilevel.xlsx", sheet(T5.4_Attachment) modify
putexcel B2=("Europe") B3=("National") B4=("Regional") 
putexcel C1=("Ile-de-France") D1=("Provence") E1=("	Bavaria	") 
putexcel F1=("Lower Saxony") G1=("Catalonia") H1=("Madrid")

*IDF
tabstat Attachment if REGIONID==201 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x //usewritten command 
putexcel C2=(x[1,1]) C3=(x[2,1]) C5=(x[3,1])

*PACA
tabstat Attachment if REGIONID==202 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel D2=(x[1,1]) D3=(x[2,1]) D5=(x[3,1])  

*Bavaria
tabstat Attachment if REGIONID==402 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel E2=(x[1,1]) E3=(x[2,1]) E4=(x[3,1]) // watchout E4!!! not E5 

*Lower Saxony 
tabstat Attachment if REGIONID==401 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel F2=(x[1,1]) F3=(x[2,1]) F4=(x[3,1]) 

*Catalonia
tabstat Attachment if REGIONID==301 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel G2=(x[1,1]) G3=(x[2,1]) G4=(x[3,1]) 

*Madrid
tabstat Attachment if REGIONID==302 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel H2=(x[1,1]) H3=(x[2,1]) H4=(x[3,1]) 

* Table 5.5 : Mean Contact rating
*putexcel set "C:\Users\Filip\Dropbox\MEDW data\Andr�'s papers\turnout Andr�\Tables_turnout_multilevel.xlsx", sheet(T5.5_Contact) modify
putexcel set "C:\Users\sgolder\Dropbox\Projects\MEDW\ch5_turnout\New folder\ch5_replication_SG\Tables_turnout_multilevel.xlsx", sheet(T5.5_Contact) modify
putexcel B2=("Europe") B3=("National") B4=("Regional") B5=("Municipal") 
putexcel C1=("Ile-de-France") D1=("Provence") E1=("	Bavaria	") 
putexcel F1=("Lower Saxony") G1=("Catalonia") H1=("Madrid")

*IDF
tabstat Contact if REGIONID==201 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x //usewritten command 
putexcel C2=(x[1,1]) C3=(x[2,1]) C5=(x[3,1])

*PACA
tabstat Contact if REGIONID==202 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel D2=(x[1,1]) D3=(x[2,1]) D5=(x[3,1])  

*Bavaria
tabstat Contact if REGIONID==402 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel E2=(x[1,1]) E3=(x[2,1]) E4=(x[3,1]) // watchout E4!!! not E5 

*Lower Saxony 
tabstat Contact if REGIONID==401 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel F2=(x[1,1]) F3=(x[2,1]) F4=(x[3,1]) 

*Catalonia
tabstat Contact if REGIONID==301 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel G2=(x[1,1]) G3=(x[2,1]) G4=(x[3,1]) 

*Madrid
tabstat Contact if REGIONID==302 [aweight= DVWEIGHT], by(LEVEL2) save 
tabstatmat x 
putexcel H2=(x[1,1]) H3=(x[2,1]) H4=(x[3,1]) 



*************
*Regressions*
*************

*Rescaling the three variables to scale ranging from 0 to 1
replace Importance=Importance/10
replace Attachment=Attachment/10
replace Impact=Impact/10
*Dropping the municipal elections in France
drop if inlist(ELECID, 22, 23)

save "C:\Users\sgolder\Dropbox\Projects\MEDW\ch5_turnout\New folder\ch5_replication_SG\chapter5.dta", replace


log close
